"""
Case Type   : xml类型
Case Name   : copy导出导入包含xml类型字段的表
Create At   : 2022/8/29
Owner       : songjing20
Description :
    1.建表并插入数据
    2.查询表
    3.copy to将表数据拷贝到文件
    4.truncate table，将文件数据导入表中，查询
    5.清理环境
Expect      :
    1.成功
    2.查询成功，3条数据
    3.成功
    4.成功，查询结果与step2中结果一致成功
    5.成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class XmlDatatype(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.table = 't_xml_0050'
        self.copy_file = os.path.join(macro.DB_BACKUP_PATH, 't_xml_0050_')
        self.format = ('text', 'csv', 'binary')

    def test_xml_datatype(self):
        text = "-----step1:建表并插入数据;expect:成功-----"
        self.log.info(text)
        sql = f'''drop table if exists {self.table} cascade;
        create table {self.table}(c_int int, c_xml xml)
        partition by range(c_int) (
          partition p1 values less than(100),
          partition p2 values less than(1000),
          partition p3 values less than(maxvalue));
        insert into {self.table} values(50, xml'<value>one</value>');
        insert into {self.table} values(666, xmlelement(name foo, 
        xmlattributes('2009-04-09 00:24:37'::timestamp as bar)));
        insert into {self.table} values(2000, 
        xml'<?xml version=\\"1.0\\" encoding=\\"UTF-8\\"?>
<ROOT>
<!-- 整体信息 -->
    <CLUSTER>
        <!-- 数据库名称 -->
        <PARAM name=\\"clusterName\\" value=\\"cluster01\\" />
        <!-- 数据库节点名称(hostname1,2) -->
        <PARAM name=\\"nodeNames\\" value=\\"hostname1\\" />
    </CLUSTER>
</ROOT>');'''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res,
                      f'建表失败:{text}')
        self.assertEqual(res.count(self.constant.INSERT_SUCCESS_MSG), 3,
                         f'insert失败:{text}')

        text = "-----step2:查询表;expect:成功，3条数据-----"
        self.log.info(text)
        select_res = self.pri_sh.execut_db_sql(
            f"select * from {self.table} order by 1;")
        self.log.info(select_res)
        self.assertIn('''50 | <value>one</value>
   666 | <foo bar="2009-04-09T00:24:37"/>
  2000 | <ROOT>                                                +
       | <!-- 整体信息 -->                                 +
       |     <CLUSTER>                                         +
       |         <!-- 数据库名称 -->                      +
       |         <PARAM name="clusterName" value="cluster01" />+
       |         <!-- 数据库节点名称(hostname1,2) -->   +
       |         <PARAM name="nodeNames" value="hostname1" />  +
       |     </CLUSTER>                                        +
       | </ROOT>
(3 rows)''', select_res, f'建表失败:{text}')

        text = "-----step3:copy to将表数据拷贝到文件;expect:成功-----"
        self.log.info(text)
        sql = '''copy {0} to '{1}{2}' with(format '{2}');
        copy {0} to '{1}{3}' with(format '{3}');
        copy {0} to '{1}{4}' with(format '{4}');'''
        res = self.pri_sh.execut_db_sql(
            sql.format(self.table, self.copy_file, *self.format))
        self.log.info(res)
        self.assertEqual(res.count('COPY 3'), 3, f'建表失败:{text}')

        text = "-----step4:truncate table，将文件数据导入表中，查询;" \
               "expect:成功,查询结果与step2中结果一致-----"
        self.log.info(text)
        for f in self.format:
            sql = f'''truncate {self.table};
            copy {self.table} from '{self.copy_file}{f}' with(format '{f}');
            select * from {self.table} order by 1;'''
            res = self.pri_sh.execut_db_sql(sql)
            self.log.info(res)
            self.assertIn(self.constant.TRUNCATE_SUCCESS_MSG, res,
                          f'建表失败:{text}')
            self.assertIn('COPY', res, f'建表失败:{text}')
            self.assertIn(select_res, res, f'建表失败:{text}')

    def tearDown(self):
        text = '----step5:清理环境 expect:成功----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.table} cascade;")
        self.log.info(res)
        cmd = f'rm -f {self.copy_file}*;'
        self.pri_node.sh(cmd).result()
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败:{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end------')
